#!usr/bin/env python3

from PyQt5.QtWidgets import (QWidget,QGridLayout,QHBoxLayout,
                            QLabel,QLineEdit,QPushButton,QFileDialog,
                            QCheckBox,QTextEdit,QComboBox,QMessageBox
                            )
from PyQt5.QtCore import Qt
import os
from widgets.BrowseWidget import BrowseWidget
from widgets.widgets import HeadLabel
import excelUtils
import re
import time

class ExcelToJson(QWidget):
    def __init__(self):
        super().__init__()
        self.setAcceptDrops(True)

        self.mainBox = QGridLayout()
        self.mainBox.setAlignment(Qt.AlignTop)
        #self.mainBox.setColumnMinimumWidth(0,650)       
        self.setLayout(self.mainBox) #定义栅格布局
        

        self.padingLeft = 30
        self.padingTop = 50
        self.curRow = 0
        localConatainer,hBox = self.__getRowLayoutInfo()        
        self.saveJsonBrowse = BrowseWidget(hBox,localConatainer.width(),localConatainer.height(),"Json保存目录：","设置生成后的Json文件保存路径")
        self.saveJsonBrowse.setPath("F:/excel_json")

        localConatainer,hBox = self.__getRowLayoutInfo()        
        self.excelBrowse = BrowseWidget(hBox,localConatainer.width(),localConatainer.height(),"批量Excel目录:","设置需要批量生成后的Json文件Excel目录")
        
        localConatainer,hBox = self.__getRowLayoutInfo()
        self.fieldRowLbl = HeadLabel("字段和数据所在行:")        
        self.fieldRowLbl.setInputText("1,2")
        self.fieldRowLbl.setToolTip("设置字段名和数据值的行索引,从0开始;例如第二、三行分别是字段名、数据值，则填:1,2只填一个值的话数据行对应加1")
        hBox.addWidget(self.fieldRowLbl,0,Qt.AlignLeft)

        self.sheetReg = HeadLabel("表名正则:")        
        self.sheetReg.setInputText("^t_")
        hBox.addWidget(self.sheetReg,0)
        self.sheetReg.setToolTip("设置后会匹配Excel文件中的所有表，只有匹配的表才会生成Json;例如:^t_ 匹配以t_开头的表")
        
        hBox.addWidget(self.fieldRowLbl,0)
        self.excelFix = HeadLabel("excel文件后缀:")     
        self.excelFix.setInputText(".xls,.xlsx")   
        hBox.addWidget(self.excelFix,0)
        self.excelFix.setToolTip("设置后会匹配批量Excel目录中后缀匹配的文件,不能为空")

        self.expButton = QPushButton()
        self.expButton.setText("导出")
        self.expButton.setToolTip("把文件拖进来点击该按钮导出")
        self.expButton.clicked.connect(self.__clickBtn)
        hBox.addWidget(self.expButton,0)

        self.autoExport = QCheckBox()
        self.autoExport.setText("自动导出")
        self.autoExport.setToolTip("把文件拖进来后是否马上导出")
        hBox.addWidget(self.autoExport,0)

        self.batButton = QPushButton()
        self.batButton.setText("批量导出")
        self.batButton.setToolTip("设置批量Excel目录后生效,对目录内所有Excel文件进行导出Json")
        self.batButton.clicked.connect(self.__clickBtn)
        hBox.addWidget(self.batButton,0)

        localConatainer,hBox = self.__getRowLayoutInfo()
        lbl = QLabel()
        lbl.setText("日志:")
        hBox.addWidget(lbl,0,Qt.AlignLeft)

        self.btnClear = QPushButton()
        self.btnClear.setText("清  空")
        self.btnClear.setToolTip("清空日志区域")
        self.btnClear.clicked.connect(self.__clickBtn)
        hBox.addWidget(self.btnClear,0,Qt.AlignRight)

        localConatainer,hBox = self.__getRowLayoutInfo()
        self.logArea = QTextEdit()
        self.logArea.setAcceptDrops(False)
        hBox.addWidget(self.logArea)
        
    def __getRowLayoutInfo(self,curRow:int=0):
        if curRow>0:
            self.curRow = curRow
        else:
            self.curRow+=1
        localConatainer = QWidget()
        hBox = QHBoxLayout()
        localConatainer.resize(300, 35)
        hBox.setAlignment(Qt.AlignTop)
        localConatainer.setLayout(hBox)
        self.mainBox.addWidget(localConatainer,self.curRow,0)
        return localConatainer,hBox
    def __clickBtn(self, *arg):
        btn = self.sender()
        if btn==self.expButton:
            if not hasattr(self,"curExportUrl") or not self.curExportUrl:
                QMessageBox.information(self,"提示","请把需要导出Json的文件拖进来")
            else:
                self.export(self.curExportUrl)
        elif btn==self.batButton:
            print("点击批量按钮")
            batRoot = self.excelBrowse.getPath()
            if not os.path.isdir(batRoot):
                QMessageBox.information(self,"提示","请先设置批量excel目录")
            else:
                self.batchExport(batRoot)
        elif btn==self.btnClear:
            self.logArea.clear()

    def dragEnterEvent(self,e):
        #拖拽进入软件区域
        print("拖拽进入软件区域")
        if e.mimeData().hasUrls():
            e.accept()

    def dropEvent(self, e):  # 放开了        
        url = e.mimeData().text()    
        url = str(url).replace("file:///", "")
        urlArr = re.split(r"\s",url)        
        fileType = os.path.splitext(url)[1]
        print("拖拽放开了 url",url,"fileType",fileType,"urlArr",urlArr)
        self.curExportUrl = url
        self.logArea.append("excel文件 {0} 准备好了".format(url))
        if not self.autoExport.isChecked():
            return
        if len(urlArr)>1:
            self.batchExportExcel(urlArr)
            return
        self.export(self.curExportUrl)

    def export(self,url):
        if not url:
            print("无效文件地址{0}".format(url))

        if os.path.isdir(url):#目录 表示执行批量
            self.batchExport(url)
        else: #表示单个文件
            self.exportJson(url)

    def batchExport(self,root):       
        excels = os.listdir(root)
        excels = [os.path.join(root,x) for x in excels]
        self.batchExportExcel(excels)

    def batchExportExcel(self,excels):
        st = time.clock()        
        c = 0
        for url in excels:
            #url = os.path.join(root,excel)
            if self.exportJson(url):
                c+=1        
        dt = time.clock() - st
        self.logArea.append("本次共处理了%d个文件,耗时%.3f秒"%(c,dt))

    def exportJson(self,fileUrl):
        if not self.isValidExcel(fileUrl):
            print("文件:",fileUrl,"并非有效的Excel文件")
            return False       
        saveFolder = self.saveJsonBrowse.getPath()
        if not os.path.isdir(saveFolder):
            QMessageBox.information(self,"提示","请先设置json保存路径")
            return False

        self.logArea.append("正在处理 {0} excel文件".format(fileUrl))       
        rowText = self.fieldRowLbl.getInputText()
        rowStrArr = rowText.split(",")
        if not rowText or ( len(rowStrArr)==1 and not re.match(r"^/d+/d+$",rowText) ):
            fieldRow = 0
            dataRow = fieldRow+1 
        elif len(rowStrArr)>=2:
            fieldRow,dataRow = [int(x) for x in rowStrArr]
        else:
            fieldRow = int(rowText)
            dataRow = fieldRow+1        
        print("fieldRow,dataRow",fieldRow,dataRow,"rowStrArr",rowStrArr)        
        jsonObj,log = excelUtils.read_to_json(fileUrl,fieldRow,dataRow,saveFolder,self.sheetReg.getInputText())
        self.logArea.append(log)
        return True

    def isValidExcel(self,fileUrl):
        '''是否是有效的Excel文件
        
        Arguments:
            fileUrl {[str]} -- [文件路径]
        
        Returns:
            [boolean] -- [是返回true]
        '''
        if not os.path.isfile(fileUrl):
            print("{0} 不是一个文件".format(fileUrl))
            return False
        fileType = os.path.splitext(fileUrl)[1]
        fileTypes = self.excelFix.getInputText()
        print("fileTypes",fileTypes)
        if not fileTypes:
            return False
        fileTypes = fileTypes.split(",")
        print("fileType,fileTypes",fileType,fileTypes,(fileType in fileTypes))
        return fileType in fileTypes
